//
//Author: Chris Russell
//
package org.seaurchin.database;
import java.sql.*;
import java.text.*;
import java.io.*;
import java.util.ArrayList;
import org.postgresql.util.PSQLException;

public class persist
{

	public static void runQuery(String theQuery)
	{
		String driver = "org.postgresql.Driver";
		String url = "jdbc:postgresql:seaurchin";
		String user = "postgres";
		String pwd = "";

		try
		{
			Class.forName( driver );	
		}
		catch( ClassNotFoundException e )
		{
			System.err.println( "Can't load driver " + e.getMessage());
			System.exit( 1 );
		}
		catch( Exception e )
		{
			System.err.println( "Can't load driver " + e.toString());
			System.exit( 1 );
		}
		
		try 
		{
			Connection con = DriverManager.getConnection( url, user, pwd );
			System.out.println( "Connection attempt successful" );
			Statement st = con.createStatement();
			System.out.println("Now executing query: \""+ theQuery + "\"\n");
			
			ResultSet rs = st.executeQuery(theQuery);
			
			if (rs != null)
			{
				while(rs.next()) {
					System.out.print("Column 1 returned ");
					System.out.println(rs.getString(1));
				}
			}
			else
			{
				System.out.println("No rows found");	
			}
			rs.close();
			st.close();
			con.close();
			
		}
		catch( Exception e )
		{
			System.err.println( "Connection attempt failed" );
			System.err.println( e.getMessage());
		}
	}
	
	public static ArrayList getSites(String category)
	{
		String driver = "org.postgresql.Driver";
		String url = "jdbc:postgresql:seaurchin";
		String user = "postgres";
		String pwd = "";

		try
		{
			Class.forName( driver );	
		}
		catch( ClassNotFoundException e )
		{
			System.err.println( "Can't load driver " + e.getMessage());
			System.exit( 1 );
		}
		catch( Exception e )
		{
			System.err.println( "Can't load driver " + e.toString());
			System.exit( 1 );
		}
		
		try 
		{
			ArrayList<String> l = new ArrayList<String>();
			String[] site_collection = new String[10];
			Connection con = DriverManager.getConnection( url, user, pwd );
			System.out.println( "Connection attempt successful" );
			Statement st = con.createStatement();
						
			String theQuery = "SELECT service_url FROM site_info a, site_categories b WHERE b.category = \'";
			theQuery += (category + "\' AND a.site_id = b.site_id");
			
			ResultSet rs = st.executeQuery(theQuery);
			
			if (rs != null)
			{
				while(rs.next()) {
					l.add(rs.getString(1));
				}
			}
			else
			{
				System.out.println("No rows found");	
			}
			rs.close();
			st.close();
			con.close();
			return l;
		}
		catch( Exception e )
		{
			System.err.println( "Connection attempt failed" );
			System.err.println( e.getMessage());
		}
		return null;
}

	public static void runUpdate(String theQuery)
	{
		String driver = "org.postgresql.Driver";
		String url = "jdbc:postgresql:seaurchin";
		String user = "postgres";
		String pwd = "";

		try
		{
			Class.forName( driver );	
		}
		catch( ClassNotFoundException e )
		{
			System.err.println( "Can't load driver " + e.getMessage());
			System.exit( 1 );
		}
		catch( Exception e )
		{
			System.err.println( "Can't load driver " + e.toString());
			System.exit( 1 );
		}
		
		try 
		{
			Connection con = DriverManager.getConnection( url, user, pwd );
			System.out.println( "Connection attempt successful" );
			Statement st = con.createStatement();
			System.out.println("Now executing query: \""+ theQuery + "\"\n");
			
			int count = st.executeUpdate(theQuery);
			
			if (count > 0)
				System.out.println("Updated " + count + " record(s)!");
			else
				System.out.println("Updated Failed!");

			st.close();
			con.close();
			
		}
		catch( Exception e )
		{
			System.err.println( "Connection attempt failed" );
			System.err.println( e.getMessage());
		}
	}
	
	
	public static void runSQLCommand(String theQuery)
	{
		String driver = "org.postgresql.Driver";
		String url = "jdbc:postgresql:seaurchin";
		String user = "postgres";
		String pwd = "";

		try
		{
			Class.forName( driver );	
		}
		catch( ClassNotFoundException e )
		{
			System.err.println( "Can't load driver " + e.getMessage());
			System.exit( 1 );
		}
		catch( Exception e )
		{
			System.err.println( "Can't load driver " + e.toString());
			System.exit( 1 );
		}
		
		try 
		{
			Connection con = DriverManager.getConnection( url, user, pwd );
			System.out.println( "Connection attempt successful" );
			Statement st = con.createStatement();
			System.out.println("Now executing query: \""+ theQuery + "\"\n");
			
			int count = st.executeUpdate(theQuery);
			
			st.close();
			con.close();
			
		}
		catch( Exception e )
		{
			System.err.println( "Connection attempt failed" );
			System.err.println( e.getMessage());
		}
	}

	
	public static void BeginTrans()
	{
		runSQLCommand("begin");
	}
	
	public static void CommitTrans()
	{
		runSQLCommand("commit");
	}
	
	public static void StorePublisherInfo(PublisherInfo info)
	{
		String theQuery;
		theQuery = "insert into publisher_info values((SELECT last_value FROM site_info_site_id_seq),\'" + info.contact_name + "\',\'" + info.title + "\',\'";
		theQuery += (info.affiliation + "\',\'" + info.phone + "\',\'" + info.email + "\')");
		runUpdate(theQuery);
	}
	
	public static void StoreSiteCategories(SiteCategories info)
	{
		String theQuery;
		theQuery = "insert into site_categories values((SELECT last_value FROM site_info_site_id_seq),\'" + info.name + "\',\'" + info.category + "\',\'" + info.taxonomyschema + "\')";
		runUpdate(theQuery);
	}
	
	public static void StoreSiteInfo(SiteInfo info)
	{
		String theQuery;
		theQuery = "insert into site_info values(nextval('site_info_site_id_seq'),\'" + info.name + "\',\'" + info.description + "\',\'" + info.service_type;
		theQuery += ("\',\'" + info.service_url + "\',\'" + info.access_info + "\')");
		runUpdate(theQuery);
	}
	
	public static void main( String args[] )
	{
		BeginTrans();
		runQuery("select contact_name from publisher_info");
		SiteInfo bob = new SiteInfo("MyWebpage", "HomePage", "Informational", "www.google.homepage.com", "OpenAccess");
		StoreSiteInfo(bob);
		PublisherInfo bob2 = new PublisherInfo();
		StorePublisherInfo(bob2);
		SiteCategories bob3 = new SiteCategories("Homepage", "Personal", "taxonomy");
		StoreSiteCategories(bob3);
		CommitTrans();
	}

}

